﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CIRDiagnoseDevice
{
    class EXCELtoSQL
    {
        string filepath;
        public EXCELtoSQL(string filepath)
        {
            this.filepath = filepath;
        }
        private DataTable xsldata(string strExcel)
        {
            DataSet ds = new DataSet();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            // string strExcel = "";
            OleDbDataAdapter myCommand = null;
            //strExcel = string.Format("select 分类代号,测试项目代号,分类,测试项目,单位,指标最低值,指标最高值,科学计数单位 ,备注,显示形式 from [{0}$] where 分类代号 is not null", "Sheet1"); //Bicycle为excel中工作薄
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, "Sheet1");
            return ds.Tables["Sheet1"];
            conn.Close();
        }
        /// <summary>
        /// 读取系统默认的配置文件
        /// </summary>
        /// <returns></returns>
        public DataTable xsldataTestConfig()
        {
            string strExcel;
            strExcel = string.Format("select 分类代号,测试项目代号,分类,测试项目,单位,指标最低值,指标最高值,科学计数单位 ,备注,显示形式 from [{0}$] where 分类代号 is not null", "Sheet1");
            return xsldata(strExcel);
        }
        /// <summary>
        /// 读取用户配置测试项目的文件
        /// </summary>
        /// <returns></returns>
        public DataTable xsldataTestConfigUser()
        {

            string strExcel;
            strExcel = string.Format("select 分类,测试项目,单位,指标最低值,指标最高值,科学计数单位 ,备注 from [{0}$] where 分类 is not null and 测试项目 is not null", "Sheet1");
            return xsldata(strExcel);

        }
        public static bool DataUpdate(DataTable dt, int batchSize)
        {
            int count = dt.Rows.Count;
            bool flag = false;
            try
            {
                string conStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source =" + Application.StartupPath + @"\" + "UserInfo.accdb" + "; Jet OLEDB:Database Password = 430510;";

                SqlConnection cn = new SqlConnection(conStr);
                SqlCommand cmd = new SqlCommand("Insert into TestTable(Id, Name) Values(@Id, @Name)", cn);
                cmd.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 20, "Name");
                cmd.UpdatedRowSource = UpdateRowSource.None;
                SqlDataAdapter da = new SqlDataAdapter();
                da.InsertCommand = cmd;
                da.UpdateBatchSize = batchSize;
                cn.Open();
                da.Update(dt);
                cn.Close();
                flag = true;
            }
            catch (Exception ex)
            {
               // LogHelper.Error(ex.Message);
                return false;
            }
            return flag;
        } 
    }
}
